capture	log close
log using MAR01-DataClean-UNODC,  replace text

//  program:    Stata 
//  task:       Merging Data Together and Cleaning  
//  project:    Market

version
clear all
macro drop _all
set linesize 80
set more off
local tag "05-05-25 "
local file "MAR01-DataClean-UNODC"
local note "|`tag' | `file'"


//	#0
//	Loading Data
insheet using 00-Pridemore-Foundation-05-31-23.csv, comma clear n
rename Nation nation

gen CID=. 
do 000-CID-nation.do
tab nation if CID==. 
order CID nation year, first 
save 00-Fraser.dta, replace 


//	#1
//	SP data
insheet using OECD-SP-Public-Total-06-27-24.csv, comma clear n 
reshape long v,  j(year) i(Country)
rename Country nation 
rename v public_sp

gen CID=. 
do 00-CID-nation.do
tab nation if CID==.
order CID nation year, first 
save SPOECD.dta, replace 

//	#2
//	LaFree Globalization Data
use KOFGI_2023_public.dta, clear 
rename code countrycode
gen CID=.
do 00-CID-CountryCode.do
rename country nation 
replace CID=642 if countrycode=="ROM"
tab nation if CID==.
drop if CID==.
order CID nation year, first 
save KOFGI.dta, replace 


//	#3
//	UNODC Data 
insheet using UNODC-Homicide-05-05-25.csv, comma clear n 

rename Iso3_code countrycode 
rename country nation 

drop if dimension!="Total"
drop if indicator!="Victims of intentional homicide"
drop if sex!="Total"
drop if age!="Total"
tab1 indicator dimension category sex age 
drop indicator dimension category sex age 

gen CID=. 
do 00-CID-CountryCode.do
tab nation if CID==.

gen TEMP=. 
replace TEMP=0 if CID!=. 
replace TEMP=1 if countrycode=="GBR_E_W"
replace TEMP=1 if countrycode=="GBR_NI"
replace TEMP=1 if countrycode=="GBR_S"
drop if TEMP==. 

preserve 
drop if TEMP==1 
drop if unitofmeasurement!="Rate per 100,000 population"
rename value rhom_un 
save X-UNODC.dta, replace 
restore 
preserve 
drop if TEMP==0
drop if unitofmeasurement=="Rate per 100,000 population"
rename value UKhom_un 
collapse (sum) UKhom_un (firstnm) region subregion source, by(year)
gen CID=826
gen countrycode="GBR" 
save UK-UNODC.dta, replace 
restore 

//	#4
// Homicide Data
use CT00-PridemoreMerge, clear 


//	#4
//	Merging data 
merge  1:1 CID year using X-UNODC.dta
drop _merge 
merge  1:1 CID year using UK-UNODC.dta
drop _merge 

merge 1:1 CID year using 00-Fraser.dta
drop _merge 
merge 1:1 CID year using SP00-PridemoreMerge.dta
drop _merge 
merge 1:1 CID year using SPOECD.dta
drop  _merge 
merge 1:1 CID year using KOFGI.dta
drop _merge 

//	#5
//	Cleaning up 
replace rhom_un=UKhom_un/pop*100000 if CID==826 & rhom_un==. 

gen HOM=. 
gen MARKET=.
gen GLOBAL=.

order  CID nation year regioncode regionname countrycode  region subregion ///
		 region_un region_un_sub region_wb eu oecd incomegroup ///
	HOM  rhom rhom_un rfhom rmhom rhom1519 rhom2024 rhom2529 rhom3034 ///
		rhom3539 rhom4044 rhom4549 rhom5054 rhom5559 rhom6064 rhom7074 ///
	MARKET  fraser heritage rol ///
	GLOBAL  KOFGI KOFGIdf KOFGIdj KOFEcGI KOFEcGIdf KOFEcGIdj ///
		KOFTrGI KOFTrGIdf KOFTrGIdj KOFFiGI KOFFiGIdf KOFFiGIdj ///
		KOFSoGI KOFSoGIdf KOFSoGIdj KOFIpGI KOFIpGIdf KOFIpGIdj ///
		KOFInGI KOFInGIdf KOFInGIdj KOFCuGI KOFCuGIdf KOFCuGIdj ///
		KOFPoGI KOFPoGIdf KOFPoGIdj ///
	PROTECT remit private_sp public_sp edu_exp healthexpend hdi ///
	 POLITIC cont_corrupt gov_eff pol_stab regulatory ruleoflaw voice_account, first 

//	#6	 
// 	save and close 
save `file'.dta, replace 
saveold `file'-old.dta, replace v(11)
log close 
//